﻿using System;
using System.Collections.Generic; 
using System.Text;
using System.Data;
using System.Data.Common;
using Runda.CMS.Common.Entity;

namespace Runda.CMS.Dal
{
    public partial class RptPutTrackStatisticsDal
    {
        public DataSet GetContractTrack(string WhereClause)
        {
            string sql = @"with T AS 
(
SELECT     A.CustomerID, B.Customername, A.InstrumentCategoryName, '' as FactSaleTrackURL,'承诺销售' AS Type, 'A' AS TypeSort, TrackYear, TrackMonth, PlanSaleAmount AS Amount    
FROM         [Cms_rpt_PutTrackStatistics] A JOIN    Cms_Ctrt_Customer B ON A.CustomerID = B.CustomerID WHERE 1=1 {0}
UNION ALL    
SELECT     A.CustomerID, B.Customername, A.InstrumentCategoryName, A.FactSaleTrackURL,'实际销售', 'B', TrackYear, TrackMonth, FactSaleAmount AS Amount    
FROM         [Cms_rpt_PutTrackStatistics] A JOIN    Cms_Ctrt_Customer B ON A.CustomerID = B.CustomerID WHERE 1=1 {0}
UNION ALL    
SELECT     A.CustomerID, B.Customername, A.InstrumentCategoryName, '','达标率', 'C', TrackYear, TrackMonth, Cast((PlanSaleAmount / FactSaleAmount)  as decimal(18,2)) AS Amount    
FROM         [Cms_rpt_PutTrackStatistics] A JOIN    Cms_Ctrt_Customer B ON A.CustomerID = B.CustomerID WHERE 1=1 {0}
)

SELECT * FROM T
UNION ALL
select A.CustomerID,B.CustomerName,'','',cast(A.TrackYear as nvarchar)+'承诺销售','D',cast(A.TrackYear as nvarchar)+'汇总','',sum(A.PlanSaleAmount) AS Amount 
from [Cms_rpt_PutTrackStatistics] A JOIN    Cms_Ctrt_Customer B ON A.CustomerID = B.CustomerID  WHERE 1=1 {0}
                      GROUP by A.TrackYear,A.CustomerID,B.CustomerName
UNION ALL
select A.CustomerID,B.CustomerName,'','',cast(A.TrackYear as nvarchar)+'实际销售','E',cast(A.TrackYear as nvarchar)+'汇总','',sum(A.FactSaleAmount) AS Amount 
from [Cms_rpt_PutTrackStatistics] A JOIN    Cms_Ctrt_Customer B ON A.CustomerID = B.CustomerID WHERE 1=1 {0}
                      GROUP by A.TrackYear,A.CustomerID,B.CustomerName
UNION ALL
select A.CustomerID,B.CustomerName,'','',cast(A.TrackYear as nvarchar)+'达标率','F',cast(A.TrackYear as nvarchar)+'汇总','', Cast((sum(A.PlanSaleAmount) / sum(A.FactSaleAmount))  as decimal(18,2)) AS Amount 
from [Cms_rpt_PutTrackStatistics] A JOIN    Cms_Ctrt_Customer B ON A.CustomerID = B.CustomerID WHERE 1=1 {0}
                      GROUP by A.TrackYear,A.CustomerID,B.CustomerName


UNION ALL
select A.CustomerID,B.CustomerName,'','','承诺销售','H','总计','',sum(A.PlanSaleAmount) AS Amount 
from [Cms_rpt_PutTrackStatistics] A JOIN    Cms_Ctrt_Customer B ON A.CustomerID = B.CustomerID   WHERE 1=1 {0}
                      GROUP by A.CustomerID,B.CustomerName
UNION ALL
select A.CustomerID,B.CustomerName,'','','实际销售','I','总计','',sum(A.FactSaleAmount) AS Amount 
from [Cms_rpt_PutTrackStatistics] A JOIN    Cms_Ctrt_Customer B ON A.CustomerID = B.CustomerID   WHERE 1=1 {0}
                      GROUP by A.CustomerID,B.CustomerName
UNION ALL
select A.CustomerID,B.CustomerName,'','','达标率','J','总计','', Cast((sum(A.PlanSaleAmount) / sum(A.FactSaleAmount))  as decimal(18,2)) AS Amount 
from [Cms_rpt_PutTrackStatistics] A JOIN    Cms_Ctrt_Customer B ON A.CustomerID = B.CustomerID   WHERE 1=1 {0}
                      GROUP by A.CustomerID,B.CustomerName
";

            sql = string.Format(sql, WhereClause);
            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);

            return CurrentDatabase.ExecuteDataSet(dbcommand);
        }
    }
}
